package kin.system.util;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.inject.Inject;

import kin.annotation.system.ExcelField;
import kin.bean.security.Role;
import kin.bean.system.Form;
import kin.bean.system.KinColumn;
import kin.bean.system.M2M;
import kin.bean.system.M2O;
import kin.bean.system.O2M;
import kin.bean.system.O2O;
import kin.dao.system.FormManagerIMP;
import kin.manager.system.FormManager;
import kin.service.system.FormServiceManager;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import org.apache.poi.ss.usermodel.CellStyle;


import org.springframework.expression.EvaluationContext;
import org.springframework.expression.Expression;
import org.springframework.expression.ExpressionParser;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;
import org.springframework.security.access.ConfigAttribute;

public class ExcelUtil_1 {

	

	public static void xlsDto2Excel(List xls, Class cls, Form form,
			ResultSet rs, String exportXlsName) throws Exception {
		// 获取总列数
		int CountColumnNum = 0;
		String className = form.getForm_name();
		// Map<String,String> names=new HashMap<String,String>();
		HSSFCell[] firstcell = null;
		HSSFCell[] secondcell = null;
		Map<String, String> hm = new HashMap<String, String>();
		// List<HSSFCell> firstcell=new ArrayList<HSSFCell>();
		// 创建Excel文档
		HSSFWorkbook hwb = new HSSFWorkbook();
		// title字体
		HSSFFont titlefont = hwb.createFont();
		titlefont.setFontName("仿宋_GB2312");
		titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titlefont.setFontHeightInPoints((short) 12);
		CountColumnNum = xls.size();
		for (KinColumn c : form.getKinColumns()) {
			if (!c.isSerializable() || !c.isHidden())
				hm.put(className + "-" + c.getFieldName(), c.getHeader());
		}

		for (M2O m2o : form.getM2os()) {
			hm.put(className + "-" + m2o.getName() + "-" + m2o.getComboName(),
					m2o.getLabel());
		}

		
		// sheet 对应一个工作页
		HSSFSheet sheet = hwb.createSheet("pldrxkxxmb");
		HSSFRow firstrow = sheet.createRow(0); // 下标为0的行开始
		HSSFRow secondrow = sheet.createRow(1);
		firstcell = new HSSFCell[1];
		secondcell = new HSSFCell[hm.size()];
		HSSFCellStyle style;
		// ---
		style = hwb.createCellStyle();
		firstcell[0] = firstrow.createCell(1);
		// 合并单元格，参数依次为起始行，结束行，起始列，结束列
		sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 1, (short) hm.size()));
		firstcell[0].setCellValue(new HSSFRichTextString("一、订单基本信息"));
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titlefont);
		sheet.autoSizeColumn(1, false);
		style.setWrapText(false);
		firstcell[0].setCellStyle(style);
		int j = 0;
        for(Map.Entry<String,String> entry : hm.entrySet()){
        	if(j<hm.size()){
        	 secondcell[j] = secondrow.createCell(j + 1);
				style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				secondcell[j].setCellValue(new HSSFRichTextString(entry.getValue()));
				secondcell[j].setCellStyle(style);
				j++;
        	}
        }
		// ---
		style = hwb.createCellStyle();
        
		j=0;
		for (int i = 0; i < CountColumnNum; i++) {
			// 创建一行
			HSSFRow row = sheet.createRow(i + 2);
			// 得到要插入的每一条记录
			Object o = xls.get(i);
			for (Map.Entry<String,String> entry : hm.entrySet()) {
				
				String key = entry.getKey().toString();
				String[] keys = key.split("-");
				HSSFCell oo = row.createCell(j + 1);
				
				if (keys.length == 2) {
					ExcelUtil_1.getExcelType(o, keys[1], oo, hwb, null);
				} else {
					Object obj = ClassUtil.getValue(o, keys[1]);
					ExcelUtil_1.getExcelType(obj, keys[2], oo, hwb, null);
				}
				sheet.autoSizeColumn(j + 1);
			
				
			}
		}

		// sheet.autoSizeColumn(1,false);
		// 创建文件输出流，准备输出电子表格
		OutputStream out = new FileOutputStream("D:/pldrxkxxmb.xls");
		hwb.write(out);
		out.close();
		System.out.println("数据库导出成功");
	}

	private static void getExcelType(Object obj, String fieldName,
			HSSFCell cell, HSSFWorkbook wb, String format) {

		EvaluationContext context = new StandardEvaluationContext(obj);
		ExpressionParser parser = new SpelExpressionParser();
		Expression exp = parser.parseExpression(fieldName);
		Object value = exp.getValue(context);
		Class clazz = exp.getValue(context).getClass();
		if (clazz.equals(String.class) || clazz.equals(Character.class)
				|| clazz.equals(char.class)) {
			if ("" == exp.getValue(context).toString()
					&& null == exp.getValue(context)) {
				cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
				cell.setCellValue("");
			} else {
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(value.toString());
			}
		} else if (clazz.equals(Integer.class) || clazz.equals(Long.class)
				|| clazz.equals(Float.class)) {
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(Double.parseDouble(value.toString()));
		} else if (clazz.equals(Boolean.class)) {
			cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
			cell.setCellValue(Boolean.valueOf(value.toString()));
		} else if (clazz.equals(Date.class)) {
			cell.setCellValue(new Date());
			HSSFCellStyle cellStyle = wb.createCellStyle();
			// 指定日期显示格式
			cellStyle.setDataFormat(HSSFDataFormat
					.getBuiltinFormat("m/d/yy h:mm"));
			cell.setCellValue((Date) value);
			// 设定单元格日期显示格式
			cell.setCellStyle(cellStyle);
		}

	}

	
	public void test(){
		
	}
	
	public static void main(String[] args) {
		List<Role> lfs = new ArrayList<Role>();
		for (int i = 0; i < 5; i++) {

			Role f = new Role();
			f.setRole_name("role_name " + i);
			f.setRole_id(i + 881);
			lfs.add(f);
		}
		try {
			xlsDto2Excel(lfs, Role.class, null, null, null);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
